﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QD.Tools;
using System.Collections;
using System.IO;

namespace QD.SQLHelper
{
    /// <summary>
    /// 通过Entity Framework访问数据库
    /// </summary>
    public class EFDbContext : DbContext
    {
        public const string enterStr = "\r\n";

        //sql关键字，不包含以下关键字的即视为不合法的sql
        private string[] sqlkey;

        //sql分割符，用户可能会输入多个sql命令，用go分割开，但.net中不支持go关键字
        private const string sqlsplitStr = "go\r\n";

        public EFDbContext(string connStr)
            : base(connStr)
        {
            Database.SetInitializer<EFDbContext>(null);
            sqlkey = new string[] { "drop", "select", "update", "delete", "insert", "create", "alter", "dbcc", "exec" };
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();//移除复数表名的契约

            base.OnModelCreating(modelBuilder);
        }

        /// <summary>
        /// 判断sql是否有效
        /// </summary>
        /// <returns></returns>
        public bool IsCorrectSQL(string sql, ref string mes)
        {
            using (SqlConnection conn = new SqlConnection(this.Database.Connection.ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    bool isCheck = true;
                    string[] sqlList = sql.Split(new string[] { sqlsplitStr }, StringSplitOptions.RemoveEmptyEntries);

                    for (int i = 0; i < sqlList.Length; i++)
                    {
                        string _sql = sqlList[i].ToLower();
                        try
                        {
                            if (_sql.IsContains(sqlkey))
                            {
                                SqlCommand cmd = new SqlCommand(_sql, conn);
                                cmd.Transaction = trans;
                                cmd.ExecuteNonQuery();
                            }
                            else
                            {
                                mes = _sql;
                                isCheck = false;
                            }
                        }
                        catch (Exception ex)
                        {
                            isCheck = false;
                            mes = ex.Message;
                        }

                        if (!isCheck)
                        {
                            break;
                        }
                    }
                    trans.Rollback();

                    return isCheck;
                }
            }
        }

        public int ExecuteNonQuery(string sql)
        {
            int i = 0;
            string _connectionString = this.Database.Connection.ConnectionString;
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                try
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    i = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return i;
        }

        public int ExecuteNonQuery(string connectionStrings, string sql)
        {
            int i = 0;
            using (SqlConnection conn = new SqlConnection(connectionStrings))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                try
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    i = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return i;
        }


        public object ExecuteScalar(string sql, string connectionString = "")
        {
            string _connectionString = connectionString;
            if (string.IsNullOrEmpty(_connectionString))
            {
                _connectionString = this.Database.Connection.ConnectionString;
            }

            object result = null;
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                try
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    result = cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return result;
        }

        /// <summary>
        /// 读取一个文件，并执行文件中的sql命令
        /// </summary>
        /// <param name="connectionStrings"></param>
        /// <param name="path"></param>
        public void ExecuteCmd(string connectionStrings, string path, System.Text.Encoding encoding)
        {
            ArrayList sqlList = new ArrayList();
            if (!File.Exists(path))
            {
                throw new Exception("未找文件" + path);
            }
            else
            {
                StreamReader rs = new StreamReader(path, encoding);//注意编码
                string commandText = "";
                string varLine = "";

                while (rs.Peek() > -1)
                {
                    varLine = rs.ReadLine();
                    if (varLine == "")
                    {
                        continue;
                    }
                    if (varLine == "GO" || varLine == "go")
                    {
                        if (!string.IsNullOrEmpty(commandText))
                        {
                            sqlList.Add(commandText);
                        }
                        commandText = "";
                    }
                    else
                    {
                        commandText += varLine;
                        commandText += enterStr;
                    }
                }

                //如果读取最后一行，则最后一行没有以go结束，将命令添加到命令集合中
                if (rs.Peek() == -1 && !string.IsNullOrEmpty(commandText))
                {
                    sqlList.Add(commandText);
                    commandText = "";
                }

                using (SqlConnection conn = new SqlConnection(connectionStrings))
                {
                    conn.Open();
                    SqlTransaction trans = conn.BeginTransaction();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.Transaction = trans;
                    try
                    {
                        foreach (string cmdText in sqlList)
                        {
                            cmd.CommandText = cmdText;
                            cmd.ExecuteNonQuery();
                        }
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                }
            }
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="isDemo">是否演示(如为true,所有结果不会真正更新到数据库)</param>
        /// <returns></returns>
        public DataTable Query(string sql, bool isDemo, string connectionString = "")
        {
            string _connectionString = connectionString;
            if (string.IsNullOrEmpty(_connectionString))
            {
                _connectionString = this.Database.Connection.ConnectionString;
            }

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string[] sqlList = sql.Split(new string[] { sqlsplitStr }, StringSplitOptions.RemoveEmptyEntries);

                        DataTable dt = new DataTable();

                        for (int i = 0; i < sqlList.Length; i++)
                        {
                            DataSet ds = new DataSet();
                            string _sql = sqlList[i];

                            //判断是否是有效的sql语句
                            if (_sql.IsContains(sqlkey))
                            {
                                SqlDataAdapter da = new SqlDataAdapter(sqlList[i], conn);
                                da.SelectCommand.Transaction = trans;
                                da.Fill(ds);

                                if (ds.Tables.Count == 0)
                                {
                                    dt = new DataTable();
                                    dt.Columns.Add("执行结果");
                                    dt.Rows.Add("命令已成功完成！");

                                }
                                else
                                {
                                    dt = ds.Tables[0];
                                }
                            }
                        }

                        if (isDemo)
                        {
                            trans.Rollback();
                        }
                        else
                        {
                            trans.Commit();
                        }
                        return dt;
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                }
            }
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable Query(string sql)
        {
            return Query(sql, false);
        }

        /// <summary>
        /// 判断用户表是否存在
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool TableExists(string tableName)
        {
            string sql = "select 1 from sys.objects where name=@p0 and Type='U'";
            DbRawSqlQuery<int?> dbRaw = this.Database.SqlQuery<int?>(sql, tableName);
            int? result = dbRaw.FirstOrDefault();
            return result != null;
        }

        /// <summary>
        /// 取对象的类型
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetObjType(string objName)
        {
            string sql = "select type from sys.objects where name=@p0";
            DbRawSqlQuery<string> dbRaw = this.Database.SqlQuery<string>(sql, objName);
            string result = dbRaw.FirstOrDefault();
            return result;
        }

        /// <summary>
        /// 取所有表信息
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IList<SysTables> Tables(string condition)
        {
            string sql = "select * from sys.tables";
            if (!string.IsNullOrEmpty(condition))
            {
                sql += (" where " + condition);
            }

            DbRawSqlQuery<SysTables> systables = this.Database.SqlQuery<SysTables>(sql);
            return systables.ToList();
        }

        /// <summary>
        /// 取所有视图信息
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IList<SysViews> Views(string condition)
        {
            string sql = "select * from sys.views";
            if (!string.IsNullOrEmpty(condition))
            {
                sql += (" where " + condition);
            }

            DbRawSqlQuery<SysViews> systables = this.Database.SqlQuery<SysViews>(sql);
            return systables.ToList();
        }

        /// <summary>
        /// 取所有数据库信息
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IList<SysDatabases> Databases()
        {
            return Databases(string.Empty);
        }

        /// <summary>
        /// 取所有数据库信息
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IList<SysDatabases> Databases(string condition)
        {
            string sql = "select * from sys.databases";
            if (!string.IsNullOrEmpty(condition))
            {
                sql += (" where " + condition);
            }

            DbRawSqlQuery<SysDatabases> sysdatabases = this.Database.SqlQuery<SysDatabases>(sql);
            return sysdatabases.ToList();
        }

        /// <summary>
        /// 取创建表脚本
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string GetTableCreateSql(string tableName)
        {
            try
            {
                if (!TableExists(tableName))
                    throw new Exception("数据库" + this.Database.Connection.Database + "中不存在表" + tableName + "");
                else
                {
                    IList<SysColumns> sysColumns = GetObjColumns(tableName);
                    StringBuilder createTableStr = new StringBuilder();
                    createTableStr.Append("create table ").Append(tableName).Append("(").Append(enterStr);
                    foreach (SysColumns colMod in sysColumns)
                    {
                        createTableStr.Append("[" + colMod.name + "] ");
                        if (colMod.is_computed)
                        {
                            createTableStr.Append(" as " + colMod.computedes);
                            if (colMod.is_persisted != null && Convert.ToBoolean(colMod.is_persisted))
                            {
                                createTableStr.Append(" PERSISTED ");
                            }
                        }
                        else
                        {
                            switch (colMod.typename)
                            {
                                case "tinyint":
                                case "smallint":
                                case "int":
                                case "bigint":
                                case "bit":
                                case "money":
                                case "smallmoney":
                                case "datetime":
                                case "smalldatetime":
                                case "image":
                                case "timestamp":
                                case "text":
                                case "xml":
                                case "uniqueidentifier":
                                case "real":
                                case "ntext":
                                case "sysname":
                                    {
                                        createTableStr.Append(" [" + colMod.typename + "] ");
                                        break;
                                    }
                                case "float":
                                case "decimal":
                                case "numeric":
                                    {
                                        createTableStr.Append(" [" + colMod.typename + "](" + colMod.precision + "," + colMod.scale + ") ");
                                        break;
                                    }
                                case "varbinary":
                                case "varchar":
                                case "binary":
                                case "char":
                                case "nvarchar":
                                case "nchar":
                                    {
                                        createTableStr.Append(" [" + colMod.typename + "](" + (colMod.max_length == -1 ? "max" : colMod.max_length.ToString()) + ") ");
                                        break;
                                    }
                            }
                            if (colMod.is_nullable)
                            {
                                createTableStr.Append(" null ");
                            }
                            else
                            {
                                createTableStr.Append(" not null ");
                            }
                            if (colMod.is_identity)
                            {
                                createTableStr.Append(" identity(1,1) ");
                            }
                            if (!string.IsNullOrEmpty(colMod.defaultName))
                            {
                                createTableStr.Append(" CONSTRAINT ").Append(" [").Append(colMod.defaultName).Append("]");
                                createTableStr.Append(" ").Append(" default (").Append(colMod.definition).Append(")");
                            }
                        }
                        createTableStr.Append(",");
                        createTableStr.Append(enterStr);
                    }
                    createTableStr.Append(")");

                    return FormatOutSql(createTableStr.ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 取对象的列信息
        /// </summary>
        /// <param name="objName"></param>
        /// <returns></returns>
        public IList<SysColumns> GetObjColumns(string objName)
        {
            string sqlcolumns = @"select a.*,b.name as typename,c.definition as computedes,c.is_persisted,
	                                d.name as defaultName,d.definition
                                    from sys.columns a 
                                    left join sys.types b on a.user_type_id=b.user_type_id 
                                    left join sys.computed_columns c on a.object_id=c.object_id and a.column_id=c.column_id
                                    left join sys.default_constraints d on a.default_object_id=d.object_id 
                                    where a.object_id=object_id('" + objName + "') order by column_id asc";

            DbRawSqlQuery<SysColumns> sysColumns = this.Database.SqlQuery<SysColumns>(sqlcolumns);
            return sysColumns.ToList();
        }

        /// <summary>
        /// 取创建表主键索引脚本
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string GetPrimaryIndex(string tableName)
        {
            IList<SysIndex> raw = GetIndexs(tableName, true);
            int rawLength = raw.Count();
            if (rawLength > 0)
            {

                SysIndex firstMod = raw.First();
                string indexname = firstMod.IndexName;
                string type_desc = firstMod.Type_Desc;
                string[] keys = raw.Select(p => p.IndexColumnName).ToArray();

                return CreatePrimaryIndex(tableName, indexname, type_desc, keys);

            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 创建主键索引
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="indexName"></param>
        /// <param name="typeDesc"></param>
        /// <param name="keys"></param>
        /// <returns></returns>
        public static string CreatePrimaryIndex(string tableName, string indexName, string typeDesc, string[] keys)
        {
            StringBuilder createIndexStr = new StringBuilder();
            createIndexStr.Append("alter table " + tableName + " add constraint " + indexName + " primary key " + typeDesc + " (");
            for (int i = 0; i < keys.Length; i++)
            {
                createIndexStr.Append(keys[i]);
                if (i != keys.Length - 1)
                {
                    createIndexStr.Append(",");
                }
            }
            createIndexStr.Append(")");

            return FormatOutSql(createIndexStr.ToString());
        }


        /// <summary>
        /// 取创建表非主键索引脚本
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string GetIndex(string tableName)
        {
            IList<SysIndex> raw = GetIndexs(tableName, false);
            if (raw.Count == 0)
            {
                return string.Empty;
            }
            else
            {
                var indexNameList = raw.Select(p => p.IndexName).Distinct();

                StringBuilder sql = new StringBuilder();
                foreach (var indexName in indexNameList)
                {
                    IList<SysIndex> indexList1 = raw.Where(p => p.IndexName == indexName).ToList();
                    string[] keys = indexList1.Select(p => p.IndexColumnName).ToArray();
                    sql.Append(CreateIndex(indexList1[0].TableName, indexList1[0].IndexName, indexList1[0].Type_Desc, keys));
                }

                return sql.ToString();
            }
        }

        /// <summary>
        /// 创建索引(非主键索引)
        /// </summary>
        /// <param name="raw"></param>
        /// <param name="sql"></param>
        /// <param name="indexName"></param>
        public static string CreateIndex(string tableName, string indexName, string typeDesc, string[] keys)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("create " + typeDesc + " index " + indexName + " on " + tableName + "(");
            for (int i = 0; i < keys.Length; i++)
            {
                sb.Append(keys[i]);
                if (i < keys.Length - 1)
                {
                    sb.Append(",");
                }
            }
            sb.Append(")");
            return FormatOutSql(sb.ToString());
        }

        /// <summary>
        /// 取表的索引
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="isPK">是否是主键索引</param>
        /// <returns></returns>
        public IList<SysIndex> GetIndexs(string tableName, bool isPK)
        {
            string sql = @"select tab.name as tablename,
                    idx.name as indexname,idx.type_desc,
                    col.name as indexcolumnname
                    from sys.indexes idx
                    inner join sys.index_columns idxCol
                     on idx.object_id=idxCol.object_id
                     and idx.index_id=idxCol.index_id
                     and idx.is_primary_key=" + (isPK ? 1 : 0) + @"
                    inner join sys.tables tab
                     on idx.object_id=tab.object_id
                    inner join sys.columns col
                      on idx.object_id=col.object_id
                     and idxCol.column_id=col.column_id where tab.name ='" + tableName + "'";

            IList<SysIndex> raw = this.Database.SqlQuery<SysIndex>(sql).ToList();

            return raw;
        }

        public SysDefaultConstraints GetDefaultConstraints(string tableName, string columnName)
        {
            string sql = string.Format(@"select a.name as tableName,b.name as columnName,c.name as consName,c.definition
                                        from　sys.tables a 
                                        left join sys.columns b on  a.object_id=b.object_id 
                                        left join sys.default_constraints c on a.object_id=c.parent_object_id and b.column_id=c.parent_column_id
                                        where a.object_id=object_id('{0}') and b.name='{1}' and c.name  is not null", tableName, columnName);

            SysDefaultConstraints mod = this.Database.SqlQuery<SysDefaultConstraints>(sql).ToList().FirstOrDefault();
            return mod;
        }

        /// <summary>
        /// 获取对象的定义文本
        /// </summary>
        /// <param name="objname"></param>
        /// <returns></returns>
        public string GetText(string objname)
        {
            string sql = @"exec sp_helptext " + objname;

            IList<string> raw = this.Database.SqlQuery<string>(sql).ToList();

            StringBuilder text = new StringBuilder();
            for (int i = 0; i < raw.Count; i++)
            {
                text.Append(raw[i]);
            }

            return FormatOutSql(text.ToString());
        }

        /// <summary>
        /// 刷新视图
        /// </summary>
        /// <returns></returns>
        public string RefreshView(string viewname)
        {
            string sql = @"
                            declare @error varchar(50)
                            begin try
                            exec sp_refreshview " + viewname + " set @error='视图" + viewname + "验证成功！' end try begin catch set @error='视图" + viewname + "验证失败！' end catch select @error as message";

            IList<string> raw = this.Database.SqlQuery<string>(sql).ToList();

            StringBuilder text = new StringBuilder();
            for (int i = 0; i < raw.Count; i++)
            {
                text.Append(raw[i]);
            }

            return text.ToString();
        }

        /// <summary>
        /// 设置所有数据库自动收缩
        /// </summary>
        public void Auto_shrink(string connectionString)
        {
            string sql = @"DECLARE @Sql VARCHAR(MAX) 
SET @Sql='' 
select @Sql=@Sql+' alter database '+'['+ name + ']'+' set auto_shrink on'+CHAR(10)+CHAR(13) from sys.databases 
where is_distributor <> 1 and is_auto_shrink_on = 0 and database_id not in (1,2,3,4) 

exec (@Sql)";

            ExecuteNonQuery(connectionString, sql);

        }

        /// <summary>
        /// 收缩数据库
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public void ShrinkDatabase(string connectionString, string databases)
        {
            string sql = string.Format(@"-- 设置为简单恢复模式
                        ALTER DATABASE [{0}] SET RECOVERY SIMPLE

                        -- 收缩日志文件
                        DBCC SHRINKDATABASE([{0}])

                        -- 恢复为完整恢复模式
                        ALTER DATABASE [{0}] SET RECOVERY FULL", databases);

            int i = ExecuteNonQuery(connectionString, sql);
        }

        /// <summary>
        /// 读取备份记录
        /// </summary>
        /// <param name="iswhere"></param>
        public DataTable backupset(string database_name, int toprows)
        {
            string sql = string.Format(@"select top {0} datediff(DAY,A.backup_finish_date,getdate()) as DDays, A.backup_set_id,A.database_name,A.user_name,A.server_name,A.machine_name,cast(A.backup_size/8/1024 as int) as backup_size,
                            A.name,A.backup_start_date,A.backup_finish_date,A.type,A.position,
                            B.physical_device_name,
                            typename=(case A.type when 'D' then '数据库' when 'I' then '差异数据库' when 'L' then '日志' end)  
                         from msdb..backupset A inner join msdb..backupmediafamily B on A.media_set_id=B.media_set_id and A.database_name='" + database_name + "' order by A.backup_set_id desc", toprows.ToString());

            DataTable dt = Query(sql);

            return dt;
        }

        /// <summary>
        /// 取磁盘空间
        /// </summary>
        /// <returns></returns>
        public DataTable xp_fixeddrives()
        {
            string sql = "EXEC master.dbo.xp_fixeddrives";
            DataTable dt = Query(sql);

            return dt;
        }

        /// <summary>
        /// 格式化输出脚本
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string FormatOutSql(string sql)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(enterStr).Append("go").Append(enterStr).Append(enterStr);
            sb.Append(sql);
            sb.Append(enterStr);

            return sb.ToString();
        }

        /// <summary>
        /// 格式化输出脚本
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static void FormatOutSql(StringBuilder sb)
        {
            StringBuilder _sb = new StringBuilder(sb.ToString());
            sb.Clear();
            sb.Append(enterStr).Append("go").Append(enterStr).Append(enterStr);
            sb.Append(_sb);
            sb.Append(enterStr);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>DataSet</returns>
        public DataSet RunProcedure(string storedProcName, SqlParameter[] parameters)
        {
            string connectionString = this.Database.Connection.ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    DataSet dataSet = new DataSet();
                    SqlDataAdapter sqlDA = new SqlDataAdapter();
                    SqlTransaction trans = null;
                    sqlDA.SelectCommand = PrepareCommand(connection, trans, storedProcName, CommandType.StoredProcedure, parameters);
                    sqlDA.Fill(dataSet);
                    connection.Close();
                    return dataSet;
                }
                catch
                {
                    throw;
                }
            }
        }

        /// <summary>
        /// 执行存储过程（无返回值）
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>
        public int RunProcedureNonQuery(string storedProcName, SqlParameter[] parameters)
        {
            int result = -1;
            string connectionString = this.Database.Connection.ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    DataSet dataSet = new DataSet();
                    SqlTransaction trans = null;
                    SqlCommand sqlCmd = PrepareCommand(connection, trans, storedProcName, CommandType.StoredProcedure, parameters);

                    result = sqlCmd.ExecuteNonQuery();
                    connection.Close();
                    return result;
                }
                catch
                {
                    throw;
                }
            }
        }

        private SqlCommand PrepareCommand(SqlConnection conn, SqlTransaction trans, string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlCommand cmd = new SqlCommand(cmdText, conn);
            cmd.CommandTimeout = 0;

            cmd.CommandType = cmdType;

            if (trans != null)
                cmd.Transaction = trans; ;

            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && parameter.Value == null)
                    {
                        parameter.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(parameter);
                }
            }

            return cmd;
        }
    }
}
